﻿using Dapper;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lrd.Permissions.CodeGenerator
{
    public class SqlServiceDbServices : DbAbstractBase, IDbServices
    {


        private readonly DbOptions _dbOptions = null;

        public  SqlServiceDbServices(IOptions<DbOptions> dbOptions)
        {

            _dbOptions = dbOptions.Value;
        }

        public IEnumerable<DbTable> GetGetDbTableAndColumns()
        {
           var dbTables = this.GetDbTables();
            foreach (var table in dbTables)
            {
                var tableColumns = this.GetDbTableColumns(table.TableName);
                var primaryKey = tableColumns.FirstOrDefault(o => o.IsPrimaryKey == true);
                table.PrimaryKeyColName = primaryKey?.ColName;
                table.PrimaryKeyType = primaryKey?.CSharpType;
                table.Columns.AddRange(tableColumns.ToList());
            }
            return dbTables;
        }
        public IEnumerable<DbTable> GetDbTables()
        {
            using (DbConnection connection=this.GetDbConnection())
            {
                string sql= @"SELECT DISTINCT d.name as TableName, f.value as TableComment
FROM sys.syscolumns AS a LEFT OUTER JOIN
                sys.systypes AS b ON a.xusertype = b.xusertype INNER JOIN
                sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
                sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
                sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
                sys.extended_properties AS f ON d.id = f.major_id AND f.minor_id = 0";
               return  connection.Query<DbTable>(sql);
            }
        }

        public IEnumerable<DbTableColumn> GetDbTableColumns(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {

                throw new Exception("表名不能为空!!!");
            }

            using (DbConnection connection = this.GetDbConnection())
            {
                string sql = @"SELECT   a.name AS ColName, CONVERT(bit, (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') 
                = 1 THEN 1 ELSE 0 END)) AS IsIdentity, CONVERT(bit, (CASE WHEN
                    (SELECT   COUNT(*)
                     FROM      sysobjects
                     WHERE   (name IN
                                         (SELECT   name
                                          FROM      sysindexes
                                          WHERE   (id = a.id) AND (indid IN
                                                              (SELECT   indid
                                                               FROM      sysindexkeys
                                                               WHERE   (id = a.id) AND (colid IN
                                                                                   (SELECT   colid
                                                                                    FROM      syscolumns
                                                                                    WHERE   (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK')) 
                > 0 THEN 1 ELSE 0 END)) AS IsPrimaryKey, b.name AS ColumnType, COLUMNPROPERTY(a.id, a.name, 'PRECISION') 
                AS ColumnLength, CONVERT(bit, (CASE WHEN a.isnullable = 1 THEN 1 ELSE 0 END)) AS IsNullable, ISNULL(e.text, '') 
                AS DefaultValue, ISNULL(g.value, ' ') AS Display
FROM      sys.syscolumns AS a LEFT OUTER JOIN
                sys.systypes AS b ON a.xtype = b.xusertype INNER JOIN
                sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
                sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
                sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
                sys.extended_properties AS f ON d.id = f.class AND f.minor_id = 0
WHERE   (b.name IS NOT NULL) AND (d.name = @TableName)
ORDER BY a.id, a.colorder";
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("TableName",tableName);
                var dbTableColumns=  connection.Query<DbTableColumn>(sql, parameters);
                dbTableColumns.ToList().ForEach(o => {
                    o.CSharpType = GetDbColumnDataType(o.ColumnType, DatabaseType.SqlServer);
                });
                return dbTableColumns;
            }

          
        }

        public DbConnection GetDbConnection()
        {
            return new SqlConnection(_dbOptions.ConnectionString);
        }


    }
}
